﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DTO;
using DAO;

namespace DAO
{
    public class HocSinhDAO
    {
        

        public HocSinhDAO()
        { 
        
        }

        #region them 1 hoc sinh
        public static bool insert(HocSinhDTO hocSinh)
        {
           if( !DaTaProvider.Checkconnect() )
               return false;

           string command, stringnrow;
           int inrow = 0;

           try
           {
               command = "select * from HocSinh";
              DataTable tb =  DaTaProvider.excecuteQuery( command );
              // inrow = DaTaProvider.ExcecuteNonQuery(command);
              inrow = tb.Rows.Count;
              inrow++;
              if (inrow > QuyDinhDAO.SiSo)
                  return false;
              DateTime dt = hocSinh.NgaySinh;
              if (DateTime.Today.Year - dt.Year < QuyDinhDAO.TuoiMin || DateTime.Today.Year - dt.Year > QuyDinhDAO.TuoiMax)
                  return false;

               stringnrow = inrow.ToString();
                               
                // insert into KetQua2( maSinhVien, maGiangKhoa, diem )
                //values ( '0212001', 'THCS01104', 6.5 )

                command = " insert into HocSinh( maHocSinh, hoTen, ngaySinh, diaChi, maLop, chucVu, gioiTinh ) values ('" + stringnrow + "',"
                 + "'" + hocSinh.TenHocSinh + "','" + hocSinh.NgaySinh + "','" + hocSinh.DiaChi + "', null, null, '" + hocSinh.GioiTinh + "')";
                DaTaProvider.ExcecuteNonQuery(command);
               
            }
             catch (Exception ex)
            {
                return false;
            }
            return true;

        }
        #endregion
        // delete from KetQua
        //where KetQua.maSinhVien = '0212001'
        #region xoa 1 hoc sinh
        public static bool delete( string maHocSinh )
        {
            try
            {

                if (!DaTaProvider.Checkconnect())
                    return false;

                string command;
                command = "delete from HocSinh where HocSinh.maHocSinh = '" + maHocSinh + "'";
                DaTaProvider.ExcecuteNonQuery(command);
                
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        #endregion
        //update KetQua
        //set KetQua.diem = 10
        //where KetQua.maSinhVien = '0212002'
        #region them lop
        public static bool ThemVaoLop( string maHocSinh, string maLop, string chucVu )
        {
            try
            {

                if (!DaTaProvider.Checkconnect())
                    return false;

                string command;

                command = "update HocSinh set HocSinh.maLop = '" + maLop + "'," + "HocSinh.chucVu = N'" + chucVu + "' where HocSinh.maHocSinh = '" + maHocSinh + "'";
                DaTaProvider.ExcecuteNonQuery(command);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region tra cuu
        public static DataTable TraCuuMaHocSinh(string maHocSinh)
        {
            
            DataTable result;

            string command = "select * from HocSinh where HocSinh.maHocSinh = '" + maHocSinh +"'";
            try
            {
                result = DaTaProvider.excecuteQuery(command);
            }
            catch (Exception)
            {
                return null;
            }

            
            return result;
        }

        public static DataTable TraCuuTenHocSinh(string tenHocSinh)
        {

            DataTable result;

            string command = "select * from HocSinh where HocSinh.hoTen like N'%" + tenHocSinh + "'";
            try
            {
                result = DaTaProvider.excecuteQuery(command);
            }
            catch (Exception ex)
            {
                return null;
            }


            return result;
        }
         #endregion

        #region Liet Ke Danh Sach Lop
        public static DataTable BangDanhSachLop(string maLop)
        {
            DataTable tb = new DataTable();

            try
            {
                string command = "select * from HocSinh where HocSinh.maLop = '" + maLop + "'";
                tb = DaTaProvider.excecuteQuery(command);
            }
            catch( Exception ex )
            {
                throw ex;
            }
            return tb;
        }
        #endregion
    }
}
